/**
 * SQLite数据库初始化模块（完整功能版）
 */
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';
import initSqlJs from 'sql.js';
import SQL_FILE_ORDER from './initializationSequence.js';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// 数据库上下文对象
const dbContext = {
    db: null,
    isDatabaseInitialized: false,
    databaseInitializationError: null,
    databaseInitializationPromise: null
};

/**
 * 执行SQL文件
 */
function executeSQLFile(db, filePath) {
    const fileName = path.basename(filePath);
    try {
        const sqlContent = fs.readFileSync(filePath, 'utf8');

        if (!sqlContent.trim()) {
            console.warn(`⚠️ [DB INIT] ${fileName} 为空文件，跳过执行`);
            return;
        }

        db.exec(sqlContent);
    } catch (error) {
        console.error(`❌ [DB INIT] ${fileName} 执行失败`, error.message);
        throw error;
    }
}

/**
 * 核心初始化方法
 */
async function initializeDatabase() {
    // 单例检查
    if (dbContext.databaseInitializationPromise) {
        return dbContext.databaseInitializationPromise;
    }

    dbContext.databaseInitializationPromise = (async () => {
        try {
            const SQL = await initSqlJs();
            dbContext.db = new SQL.Database();

            console.log('🔵 [DB INIT] 开始数据库事务');
            dbContext.db.exec("BEGIN TRANSACTION;");

            for (const file of SQL_FILE_ORDER) {
                const filePath = path.join(__dirname, '../../sql', file);
                if (!fs.existsSync(filePath)) {
                    throw new Error(`❌ 缺失SQL文件: ${file}`);
                }
                executeSQLFile(dbContext.db, filePath);
            }

            dbContext.db.exec("COMMIT;");
            dbContext.isDatabaseInitialized = true;
            console.log('🟢 [DB INIT] 数据库初始化完成');
            return dbContext.db;
        } catch (error) {
            if (dbContext.db) {
                console.log('🔴 [DB INIT] 执行回滚');
                dbContext.db.exec("ROLLBACK;");
            }
            dbContext.databaseInitializationError = error;
            console.error('❌ [DB INIT] 初始化失败', error);
            throw error;
        }
    })();

    return dbContext.databaseInitializationPromise;
}

// 立即执行初始化
initializeDatabase().catch(err => {
    dbContext.databaseInitializationError = err;
});

dbContext.databaseInitializationPromise = initializeDatabase().catch(err => {
    dbContext.databaseInitializationError = err;
    console.error('❌ 数据库初始化失败:', err);
});

export { dbContext, initializeDatabase };